<?php
	header('content-type:text/html;charset=utf-8'); 
	$GongXu = $_POST["GongXu"];//工序
	$BanBie = $_POST["BanBie"];//班別
	$DT = '2018-5-25';
	// if ($BanBie === "B" && date("G")<20){
		// $DT=date("Y-m-d",strtotime("-1 day"));
	// }else{
		// $DT=date("Y-m-d");
	// }
	$ZhiBiao = $_POST["ZhiBiao"];//製表人
	$Arr = $_POST["ShuZu"];//數組字串
	$Arr = json_decode($Arr,true);//傳回來的字串,需要調整,傳回來的字串轉Json數組
	$ChanPinXingHao = "";
	$DingDanHao = "";
	$QTY = 0;
	$pre = "";
	$ISDID = "";
	$ISHID = "";
	$pdo =new PDO("mysql:host=172.31.3.21;port=3306;dbname=tddb;","root","tdled2018");
	$pdo->query("set character set 'utf8'");//读库 
	$pdo->query("set names 'utf8'");//写库 
	$cnt=count($Arr);
	//先查表頭ID,然後返回表頭ID,如果存在就UPDATE,不存在就INSERT INTO后取回insert_id
	$SQL = "
	SELECT
		HID
	FROM
		d_h_temp
	WHERE
		GongXu='".$GongXu."'
	AND
		RiQi='".$DT."'
	AND
		BanBie='".$BanBie."'
	AND
		ZhiBiao='".$ZhiBiao."'
	";
	$result = $pdo->query($SQL);//查詢HID
	$i = $result->rowCount();//i=結果集行數,小於1條就是沒有
	if ($i>0){//大於0,存在,那麼取出結果集裡面的HID并打印調試它
		while ($row = $result->fetch()){
			$HID = $row['HID'];
		}
	}else{//否則就是小於或=0,那麼就INSERT INTO后取回insert_id
		$SQL = "
		INSERT INTO 
			d_h_temp( 
				GongXu, RiQi, BanBie, ZhiBiao) 
		VALUES (
			'".$GongXu."',
			'".$DT."',
			'".$BanBie."',
			'".$ZhiBiao."'
		)";
		$result = $pdo->query($SQL);
		$HID = $pdo->lastInsertId();
	}
	$WaiXingChiCun = null;
	$ChanPinXiLie = null;
	$ChanPinLeiXing=null;
	$ChanPinXingHao=null;
	$TouChanQty=null;
	
	$dbName = "sqlsrv:Server=172.31.3.12,1433;Database=UFDATA_001_2017";
	$dbUser = "sa";
	$dbPassword = "TDled!2018";
	$db = new PDO($dbName, $dbUser, $dbPassword);
	
	for ($x=0; $x<$cnt; $x++) {
		//2018 03 21 加上投產數，為了 WIP 表的產生2018-3-21 22:51:03注释:已加
		//2018-3-23 18:31:39注释:实测发现工单号为无时无法运行,增加针对无的Delete和过滤
		if($Arr[$x][6] == '无' and $Arr[$x][0] != ""){
			$sql = 
			"DELETE
			FROM 
				d_d_temp 
			WHERE
				DID='".$Arr[$x][0]."'"
			;
			$result = $pdo->query($sql);
		}else{
			$sql = "
			SELECT
				 InvCode
				,OrderCode
				,Qty
			FROM
				mom_orderdetail
			WHERE
				MoLotCode like '".substr($Arr[$x][6],0,9)."%'
			AND
				RelsDate<>''"
			;  //2018-3-12 10:49:59注釋:用工單號從庫里取出產品型號和訂單號,審核日期不等於空白.
			
			$res = $db->query($sql);
			$InvCode = null;
			while ($row = $res->fetch()){
				$ChanPinXingHao=$row['InvCode'];//成品型號
				$DingDanHao=$row['OrderCode'];//訂單號
				$QTY=$row['Qty'];//Qty 生产订单数量 //投產數(工單量)
			}
			//这里判断keepwo表里面是否存在工单号
			$sql = 
			"SELECT 
				 GongDan,
				TouChanQty
			FROM 
				keepwo
			WHERE
				GongDan = '".$Arr[$x][6]."'"
			;
			$result = $pdo->query($sql);//查询工单号是否已经登记过了
			if ($result->rowCount() < 1){//如果工单不存在,就进行写入
				$sql = 
				"INSERT INTO
					keepwo(
						GongDan,
						ChanPinXingHao,
						TouChanQty
					)
				VALUES
					(
						'".$Arr[$x][6]."',
						'".$ChanPinXingHao."',
						".$QTY."
					)"
				;
			}else{
				while ( $row = $res->fetch()){
					$TouChanQty=$row['TouChanQty'];
				}
				if ($TouChanQty !== $QTY){
					$sql = 
					"UPDATE
						keepwo
					SET
						ChanPinXingHao='".$ChanPinXingHao."',
						TouChanQty='".$QTY."'
					WHERE
						GongDan='".$Arr[$x][6]."'"
					;
				}
			}
			$result = $pdo->query($sql);//写入MySQL的keepwo
			
			$sz = substr($ChanPinXingHao,1,2); //成品型號 ，第2位，截2碼
			$BuChongMa = substr($ChanPinXingHao,8,1);//成品型號 ，第9位，截1碼
			//2018-3-9 16:27:52注釋:備份原語句,採用聯查補充碼形式:$sql = "select WaiXingChiCun, ChanPinLeiXing FROM waixingdaima WHEREwaixingdaima ='".$sz."' ";
			//取出外形尺寸和補充碼
			$sql = 
			"SELECT
				WaiXingChiCun, ChanPinLeiXing, XiLieQianZhuiMa
			FROM
				waixingdaima,buchongma
			WHERE
				WaiXingDaiMa ='".$sz."'
			AND	
				BuChongMa ='".$BuChongMa."'"
			;
			$result = $pdo->query($sql);//取出外形尺寸和補充碼
			
			//2018-3-8 18:40:50注釋:先搜尋補充碼,補充碼有的就直接截取補充碼表的前綴碼,如果找不到就一定是工單的第二碼.
			if ($result->rowCount() < 1){
				//如果結果集行數小於1,那麼就是找不到補充碼,直接用原來的工單號第二碼形式
				$result = null;//如果沒搜到就重置結果集	
				$sql = 
				"SELECT
					WaiXingChiCun,
					ChanPinLeiXing
				FROM
					waixingdaima
				WHERE
					WaiXingDaiMa ='".$sz."'"
				;
				$result = $pdo->query($sql);
				
				while ($row = $result->fetch()){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
				}
				
				$pre=substr($Arr[$x][6],1,1);//前綴碼=工單號,第二位,截1碼(這裡需要區分常規或客製化工單號)
				
			}else{//大於等於1時,產品系列+類型+系列前綴
				while ($row = $result->fetch()){
					$WaiXingChiCun=$row['WaiXingChiCun'];
					$ChanPinLeiXing=$row['ChanPinLeiXing'];
					$pre=$row['XiLieQianZhuiMa'];
				}
			}
			
			if ($pre == "K") { $pre ="SSC";}//如果K,就是首爾
			$ChanPinXiLie =$pre.$WaiXingChiCun; 
			//這裡是原來的循環植入
			//這裡要改成PDO取出的訂單號
			//這裡要改成PDO取出的產品型號
			//這裡要改成PDO取出的產品類型
			//這裡要改成PDO取出的產品系列
			//已改完
			if($i>0 && $Arr[$x][0]<> ""){//大於0時就有表頭數據,UPDATE
				$SQL = "
				UPDATE
					d_d_temp
				SET
					HID=".$Arr[$x][1].",
					DingDanHao='".$DingDanHao."',
					ChanPinXingHao='".$ChanPinXingHao."',
					ChanPinLeiXing='".$ChanPinLeiXing."',
					ChanPinXiLie='".$ChanPinXiLie."',
					GongDanHao='".$Arr[$x][6]."',
					JiTai='".$Arr[$x][7]."',
					JiHuaQTY=".$Arr[$x][8].",
					JiHuaTime=".$Arr[$x][9].",
					CaoZuoYuan='".$Arr[$x][10]."',
					ShiJiQTY=".$Arr[$x][11].",
					ShiJiTime=".$Arr[$x][12].",
					UPH=".$Arr[$x][13].",
					DaiJiTime=".$Arr[$x][14].",
					DianJianTime=".$Arr[$x][15].",
					HuanDanTime=".$Arr[$x][16].",
					HuanHaoCaiTime=".$Arr[$x][17].",
					QingYeJuTime=".$Arr[$x][18].",
					ShouJianTime=".$Arr[$x][19].",
					TiaoJiTime=".$Arr[$x][20].",
					GongChengShiYangiTime=".$Arr[$x][21].",
					GuZhangTime=".$Arr[$x][22].",
					PinZhiYiChangTime=".$Arr[$x][23].",
					DaiLiaoTime=".$Arr[$x][24].",
					ShuiDianQiYiChangiTime=".$Arr[$x][25].",
					QiTaJiHuaWaiTingJiTime=".$Arr[$x][26].",
					WeiDaChengYuanYinBeiZhu='".$Arr[$x][27]."'
				WHERE
					DID=".$Arr[$x][0]
				;
				$result = $pdo->query($SQL);
			}elseif($i>0 && $Arr[$x][0]===""){//小於等於0時,湊Insert多條語句,然後在PDO_End後面一次Insert
				//PHP FOR 語法:for ($x=0; $x<=10; $x++) {
				// for ($k=2;$k<=28;$k++){
					// $ISDID .= $Arr[$x][$k]."','"
				// };
				$ISDID .= "(
				'".$HID."',
				'".$DingDanHao."',
				'".$ChanPinXingHao."',
				'".$ChanPinLeiXing."',
				'".$ChanPinXiLie."',
				'".$Arr[$x][6]."',
				'".$Arr[$x][7]."',
				'".$Arr[$x][8]."',
				'".$Arr[$x][9]."',
				'".$Arr[$x][10]."',
				'".$Arr[$x][11]."',
				'".$Arr[$x][12]."',
				'".$Arr[$x][13]."',
				'".$Arr[$x][14]."',
				'".$Arr[$x][15]."',
				'".$Arr[$x][16]."',
				'".$Arr[$x][17]."',
				'".$Arr[$x][18]."',
				'".$Arr[$x][19]."',
				'".$Arr[$x][20]."',
				'".$Arr[$x][21]."',
				'".$Arr[$x][22]."',
				'".$Arr[$x][23]."',
				'".$Arr[$x][24]."',
				'".$Arr[$x][25]."',
				'".$Arr[$x][26]."',
				'".$Arr[$x][27]."'
				),"
				;
			}else{//排他后:第一次建表頭ID,建完后還是會取回HID的情況
				$ISDID.="(
				'".$HID."',
				'".$DingDanHao."',
				'".$ChanPinXingHao."',
				'".$ChanPinLeiXing."',
				'".$ChanPinXiLie."',
				'".$Arr[$x][6]."',
				'".$Arr[$x][7]."',
				'".$Arr[$x][8]."',
				'".$Arr[$x][9]."',
				'".$Arr[$x][10]."',
				'".$Arr[$x][11]."',
				'".$Arr[$x][12]."',
				'".$Arr[$x][13]."',
				'".$Arr[$x][14]."',
				'".$Arr[$x][15]."',
				'".$Arr[$x][16]."',
				'".$Arr[$x][17]."',
				'".$Arr[$x][18]."',
				'".$Arr[$x][19]."',
				'".$Arr[$x][20]."',
				'".$Arr[$x][21]."',
				'".$Arr[$x][22]."',
				'".$Arr[$x][23]."',
				'".$Arr[$x][24]."',
				'".$Arr[$x][25]."',
				'".$Arr[$x][26]."',
				'".$Arr[$x][27]."'
				),"
				;
			}
		}
		//这里是keepwip的开始
		$SQL = "
		SELECT
			*
		FROM
			keepwip
		WHERE
			GongXu='".$GongXu."'
		AND
			RiQi='".$DT."'
		AND
			GongDan='".$Arr[$x][6]."'"
		;
		$result = $pdo->query($SQL);//查詢WIP表的唯一值是否存在
		$i = $result->rowCount();//i=結果集行數,小於1條就是沒有
		$SUM = $Arr[$x][11];//SUM=JSON每行的数量
		$WCSQL = "";//完成數語句
		$LJSQL = "";//累計數語句
		$WCSUM = 0;//今日完成
		$LJSUM = 0;//累计完成
		if ($i>0){
			$WCSQL = "
			SELECT
				SUM(ShiJiQTY) AS SJSUM
			FROM
				d_d_temp ,d_h_temp
			WHERE
				d_d_temp.HID =d_h_temp.HID
			AND
				GongDanHao = '".$Arr[$x][6]."'
			AND
				GongXu = '".$GongXu."'
			AND
				RiQi = '".$DT."'"
			;
			$result = $pdo->query($WCSQL);//查詢今天的d_d_temp總完成數,然後讀出并寫入到WIP表
			while ($row = $result->fetch()){
				$WCSUM = $row['SJSUM'];
			}
			//累計完成開始
			$LJSQL = "
			SELECT
				SUM(ShiJiQTY) AS SJSUM
			FROM
				d_d_temp ,d_h_temp
			WHERE
				d_d_temp.HID =d_h_temp.HID
			AND
				GongDanHao = '".$Arr[$x][6]."'
			AND
				GongXu = '".$GongXu."'"
			;
			//echo "LJSQL=".$LJSQL."<br/>";
			
			$result = $pdo->query($LJSQL);//查詢小於等於今天的d_d_temp總完成數,然後讀出并寫入到WIP表
			
			while ($row = $result->fetch()){
				$LJSUM = $row['SJSUM'];
			}
			$SQL=
			"UPDATE
				keepwip
			SET
				WanChengQTY='".$WCSUM."',
				LeiJiQTY='".$LJSUM."'
			WHERE
				GongXu='".$GongXu."'
			AND
				RiQi='".$DT."'
			AND
				GongDan='".$Arr[$x][6]."'"
			;
		}else{//否則就是小於0,那麼就INSERTINTO
			$SQL=
			"INSERT INTO
				keepwip
				(
					 GongXu
					,RiQi
					,GongDan
					,ChanPinXingHao
					,WanChengQTY
					,LeiJiQTY
				)
			VALUES 
				(
					 '".$GongXu."'
					,'".$DT."'
					,'".$Arr[$x][6]."'
					,'".$ChanPinXingHao."'
					,'".$SUM."'
					,'".$SUM."'
				)
			"
			;
		}
		$result = $pdo->query($SQL);//更新或寫入wip表
	}
	$ISDID=substr($ISDID,0,strlen($ISDID)-1);
	$SQL = "
	INSERT INTO
		d_d_temp(HID, DingDanHao, ChanPinXingHao, ChanPinLeiXing, ChanPinXiLie, GongDanHao, JiTai, JiHuaQTY, JiHuaTime, CaoZuoYuan, ShiJiQTY, ShiJiTime, UPH, DaiJiTime, DianJianTime, HuanDanTime, HuanHaoCaiTime, QingYeJuTime, ShouJianTime, TiaoJiTime, GongChengShiYangiTime, GuZhangTime, PinZhiYiChangTime, DaiLiaoTime, ShuiDianQiYiChangiTime, QiTaJiHuaWaiTingJiTime, WeiDaChengYuanYinBeiZhu) 
	VALUES ".
		$ISDID
	;
	$result = $pdo->query($SQL);
	$sql = 
	"SELECT 
		DID, 
		d_d_temp.HID, 
		DingDanHao, 
		ChanPinXingHao, 
		ChanPinLeiXing, 
		ChanPinXiLie, 
		GongDanHao, 
		JiTai, 
		JiHuaQTY, 
		JiHuaTime, 
		CaoZuoYuan, 
		ShiJiQTY, 
		ShiJiTime, 
		UPH, 
		DaiJiTime, 
		DianJianTime, 
		HuanDanTime, 
		HuanHaoCaiTime, 
		QingYeJuTime, 
		ShouJianTime, 
		TiaoJiTime, 
		GongChengShiYangiTime, 
		GuZhangTime, 
		PinZhiYiChangTime, 
		DaiLiaoTime, 
		ShuiDianQiYiChangiTime, 
		QiTaJiHuaWaiTingJiTime, 
		WeiDaChengYuanYinBeiZhu
	FROM 
		d_h_temp,d_d_temp 
	WHERE 
		d_h_temp.HID = d_d_temp.HID and RiQi ='".$DT."' and GongXu = '".$GongXu."' and BanBie ='".$BanBie."' and ZhiBiao ='".$ZhiBiao."'"
	;
	$result = $pdo->query($sql); 
	$dbdata = array();
	while ( $row = $result->fetch())  {
		$dbdata[]=$row;
	}
	echo json_encode($dbdata);